<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\AppBaseController;
use Illuminate\Http\Request;
use App\Models\{DateCollection,FlowCollection,ParkInfo,PayFee,ChargerRotation};
use Schema;
use App\Models\ParkRecord;
use Excel;
use Illuminate\Support\Facades\Storage;
use App\Services\BankServices;

class DataReportAPIController extends AppBaseController
{
    private $park_arr = [];
    use BankServices;

    public function __construct()
    {
        $park_arr = $this->getUserParks();
        $park_arr = $this->getParkCodes($park_arr);
        $this->park_arr = $park_arr;
    }

    /**
     *  线上支付对账 汇总
     *
     * @param Request $request
     * @return mixed
     */
    public function index(Request $request)
    {
        $input = $request->all();

        // 获取车场记录
        $d = new PayFee();

        $common_model = $d;  // 本期

        $all_model = $common_model;
        // 往期时间
        if (isset($input['vtime']) && !empty($input['vtime'])) {
            $last_time = $input['vtime'];
            if (isset($last_time[0]) && !empty($last_time[0])) {
                $start_time = strpos($last_time[0], '+') ? str_replace('+', '', $last_time[0]) : $last_time[0];
                $start_time = strtotime($start_time);
                $all_model = $all_model->where("pay_time", '>=', $start_time);
            }
            if (isset($last_time[1]) && !empty($last_time[1])) {
                $start_time = strpos($last_time[1], '+') ? str_replace('+', '', $last_time[1]) : $last_time[1];
                $start_time = strtotime($start_time);
                $all_model = $all_model->where("pay_time", '<', $start_time);
            }
        }
        $pay_type = config('custom.pay_type');
        $pay_name = config('custom.pay_name');
        unset($pay_name[1]);
        unset($pay_type['cash']);
        if (isset($input['park_code']) && !empty($input['park_code'])) {
            $all_model = $all_model->whereIn("park_code",$input['park_code']);
        } else {
            $all_model = $all_model->whereIn("park_code", $this->park_arr);
        }
        $res = $all_model->whereIn('pay_type',$pay_type)
            ->select(
                [
                    "pay_type",
                    \DB::raw("count(1) as count"),
                    \DB::raw("SUM(pay_amount)/100 as amount"),
                ]
            )
            ->groupBy("pay_type")
            ->get();
        $re_pay_type = array_flip($pay_type);
        $data = [];
        foreach ($res as $re) {
            if(in_array($re->pay_type, $pay_type)){
                $tmp = [];
                $tmp['title'] = $pay_name[$re->pay_type] ;
                $tmp['count'] = $re->count;
                $tmp['amount'] = re2float($re->amount);
                $data[$re->pay_type] = $tmp;
            }
        }
        foreach ($pay_name as $k=>$p) {
            if(!isset($data[$k])){
                $tmp = [];
                $tmp['title'] = $p ;
                $tmp['count'] = 0;
                $tmp['amount'] = '0.00';
                $data[$k] = $tmp;
            }
        }

        $r = [];
        $total_amount = 0;
        $total_count = 0;
        foreach ($data as $d) {
            $total_count += $d['count'];
            $total_amount += $d['amount'];
            $r[] = $d;
        }
        $tmp['title'] = "合计";
        $tmp['count'] = $total_count;
        $tmp['amount'] = re2float($total_amount);
        $c = collect($r);
        $c->prepend($tmp);
        return $this->sendResponse($c, '查询成功');
    }

    /**
     * 线上支付对账 明细
     *
     * @param Request $request
     * @return mixed
     */
    public function pay_fee(Request $request)
    {
        $input = $request->all();
        $page = $request->input("page", 1);
        $pages = $request->input("limit", 15);
        $all_pay_type = config('custom.pay_name');
        // 获取车场记录
        $p = new PayFee();

        // 获取车场范围
        if (isset($input['park_code']) && !empty($input['park_code'])) {
            $p = $p->whereIn("park_code",$input['park_code']);
        } else {
            $p = $p->whereIn("park_code", $this->park_arr);
        }
        // 支付时间
        if (isset($input['vtime']) && !empty($input['vtime'])) {
            $pay_time = $input['vtime'];
            if (isset($pay_time[0]) && !empty($pay_time[0])) {
                $pay_time[0] = strpos($pay_time[0], '+') ? str_replace('+', '', $pay_time[0]) : $pay_time[0];
                $start_time = strtotime($pay_time[0]);
                $p = $p->where("pay_time", '>=', $start_time);
            }
            if (isset($pay_time[1]) && !empty($pay_time[1])) {
                $pay_time[1] = strpos($pay_time[1], '+') ? str_replace('+', '', $pay_time[1]) : $pay_time[1];
                $start_time = strtotime($pay_time[1]);
                $p = $p->where("pay_time", '<', $start_time);
            }
        }
        $p = $p->whereIn("pay_type", [0,2,3,4,5]);

        $file_name = md5('rfee_' . json_encode($input)) . ".csv";
        if (isset($input['export']) && !empty($input['export'])) {
            if (file_exists(storage_path("app/public/" . $file_name))) {
                $res = [];
                $res['file_name'] = $file_name;
                return $this->sendResponse($res, '查询成功');
            }
            $record = $p->get();
        } else {
            $record = $p->orderBy("id", "desc")->paginate($pages, array("*"), "page", $page);
        }

        Schema::connection("local_config");
        foreach ($record as &$val) {
            $val->pay_amount = number_format($val->pay_amount / 100, 2, '.', '');
            $val->parking_duration = sec2time($val->out_time - $val->in_time);
            $val->pay_type_name = isset($all_pay_type[$val->pay_type]) ? $all_pay_type[$val->pay_type] : '';
            if(substr($val->pay_order,0,2) == '98'){
                $val->pay_type_name = '无感支付';
                $val->pay_order = $val->pay_src_order;
            }
            // 获取车主名
            if (Schema::hasTable("t_card_info_" . $val->park_code)) {
                $v = new CardInfo();
                $v->setTable("t_card_info_" . $val->park_code);
                $park_code = "'%" . trim(json_encode($val->vpl_number), '"') . "%'";
                $owner = $v->where("vpl_number", "like", $park_code)->value("username");
                if (isset($owner) && !empty($owner)) {
                    $val->vpl_owner = $owner ? $owner : '';
                }
            } else {
                $val->vpl_owner = '';
            }
            $val->pay_time = $val->pay_time ? date("Y-m-d H:i:s", $val->pay_time) : '';
            $val->in_time = $val->in_time ? date("Y-m-d H:i:s", $val->in_time) : '';
            $val->out_time = $val->out_time ? date("Y-m-d H:i:s", $val->out_time) : '';
            $park_name = ParkInfo::where("park_code", $val->park_code)->value("parking_name");
            $val->park_name = $park_name ? $park_name : '';
            $val->pay_src_amount = number_format($val->pay_src_amount / 100, 2, '.', '');
        }
        if (isset($input['export']) && !empty($input['export'])) {
            set_time_limit(1);
            $title = ['pay_order' => '订单号', 'pay_time' => '支付时间', 'in_time' => '车牌号码', 'park_name' => '车场名称',
                'out_time' => '进场时间', 'parking_duration' => '出场时间', 'status' => '停车时长', 'parking_charge' => '车主名称',
                'pay_src_amount' => '支付金额（元）', 'pay_type_name' => '支付方式', 'charge_info' => '收费员'];
            $type_arr = config('custom.auth_type');
            $export_data = [];
            foreach ($record as $val) {
                $tmp = [];
                $tmp['pay_order'] = $val['pay_order'];
                $tmp['pay_time'] = $val['pay_time'] . ' ';
                $tmp['vpl_number'] = $val['vpl_number'];
                $tmp['park_name'] = $val['park_name'];
                $tmp['in_time'] = $val['in_time'] . ' ';
                $tmp['out_time'] = $val['out_time'] . ' ';
                $tmp['parking_duration'] = $val['parking_duration'];
                $tmp['vpl_owner'] = $val['vpl_owner'];
                $tmp['pay_src_amount'] = $val['pay_src_amount'];
                $tmp['pay_type_name'] = $val['pay_type_name'];
                $tmp['charger'] = $val['charger'];
                $export_data[] = $tmp;
            }
            return $this->export($export_data, $title, $file_name);
        }
        return $this->sendResponse($record, '查询成功');

    }

    /*
     *  收入合计
     *
     */
    function charger_fee(Request $request)
    {
        $input = $request->all();
        $page = $request->input("page", 1);
        $pages = $request->input("limit", 15);

        $model  = ChargerRotation::select("*");
        // 往期时间
        if (isset($input['vtime']) && !empty($input['vtime'])) {
            $last_time = $input['vtime'];

            if (isset($last_time[0]) && !empty($last_time[0])) {
                $start_time = $last_time[0];
                $model = $model->where("start_time", '>=', $start_time);
            }
            if (isset($last_time[1]) && !empty($last_time[1])) {
                $start_time = $last_time[1];
                $model = $model->where("start_time", '<=', $start_time);
            }
            if (isset($last_time[0]) && !empty($last_time[0])) {
                $start_time = $last_time[0];
                $model = $model->where("end_time", '>=', $start_time);
            }
            if (isset($last_time[1]) && !empty($last_time[1])) {
                $start_time = $last_time[1];
                $model = $model->where("end_time", '<=', $start_time);
            }
        }
        if (isset($input['park_code']) && !empty($input['park_code'])) {
            $model = $model->where("park_code", $input['park_code']);
        }

        $p = $model;

        $file_name = md5('rcharger_' . json_encode($input)) . ".csv";
        if (isset($input['export']) && !empty($input['export'])) {
            if (file_exists(storage_path("app/public/" . $file_name))) {
                $res = [];
                $res['file_name'] = $file_name;
                return $this->sendResponse($res, '查询成功');
            }
            $record = $p->get();
        } else {
            $record = $p->orderBy('id', "desc")->orderBy('charger_account','asc')->paginate($pages, array("*"), "page", $page);
        }
        $t = $model;
        $v = new ParkRecord();
        $v->setTable("t_park_record_{$input['park_code']}");

        foreach ($record as $value) {
            $total_cash = 0;
            $total_union = 0;
            $value->lift_num = 0;

            $value->lift_num = $v->where("charge_items", 'like','%charger":"'.$value->charger_account."%")
                ->where("charge_items", "like",'%"charge_type":8,%')
                ->where("out_time", ">=",$value->start_time)
                ->where("out_time", "<=",$value->end_time)
                ->count();
            $tmp = $v->select([
                \DB::raw("sum(calc_charge) as calc_charge"),
                \DB::raw("sum(parking_charge) as total_charge"),
            ])
                ->where("charge_items", 'like','%charger":"'.$value->charger_account."%")
                ->where("charge_items", "like",'%charge_type":1,%')
                ->where("out_time", ">=",$value->start_time)
                ->where("out_time", "<=",$value->end_time)
                ->first();
            $value->yingshou = re2float($tmp->calc_charge/100);
            $value->shishou = re2float($tmp->total_charge/100);
            // 实收现金
            $tmp = $v->where("charge_items", 'like','%charger":"'.$value->charger_account."%")
                ->where("charge_items", "like",'%charge_type":1,%')
                ->where("out_time", ">=",$value->start_time)
                ->where("out_time", "<=",$value->end_time)
                ->sum('parking_charge');
            $value->total_cash = re2float($tmp/100);

            // 实收银联
            $tmp = $v->where("charge_items", 'like','%charger":"'.$value->charger_account."%")
                ->where("charge_items", "like",'%charge_type":50%')
                ->where("out_time", ">=",$value->start_time)
                ->where("out_time", "<=",$value->end_time)
                ->sum('total_charge');
            $value->total_union = re2float($tmp/100);
            $value->charger_name = $value->charger_name.'('.$value->charger_account.')';

            $value->start_time = date("Y-m-d H:i:s",$value->start_time);
            $value->end_time = date("Y-m-d H:i:s",$value->end_time);
        }
        if(isset($input['export']) && !empty($input['export'])){
            $title = ['charger_name'=>'收费员','start_time'=>'上班时间','end_time'=>'下班时间','lift_num'=>'免费开闸次数',
                'yingshou'=>'应收','total_cash'=>'实收现金','total_union'=>'实收银联','shishou'=>'实收合计'];
            $export_data = [];
            foreach ($record as $val) {
                $tmp = [];
                $tmp['charger_name'] = $val['charger_name'];
                $tmp['start_time'] = $val['start_time'];
                $tmp['end_time'] = $val['end_time'];
                $tmp['lift_num'] = $val['lift_num'];
                $tmp['yingshou'] = $val['yingshou'];
                $tmp['total_cash'] = $val['total_cash'];
                $tmp['total_union'] = $val['total_union'];
                $tmp['shishou'] = $val['shishou'];
                $export_data[] = $tmp;
            }
            return $this->export($export_data,$title,$file_name);
        }
        return $this->sendResponse($record, '查询成功');
    }

    /**
     *  对账excel
     */
    public function dayDayFee(Request $request)
    {
        set_time_limit(0);
        $input = $request->all();
        $all_pay_type = config('custom.pay_name');
        $filePath = storage_path() . '/app/uploads/'.$input['file'];
        if(!file_exists($filePath)){
            return $this->sendError('文件不存在');
        }
        Excel::load($filePath, function ($reader) use ($input,$filePath) {
            $reader = $reader->getSheet(0);
            $resBank = $reader->toArray();
            // 0 序号  1商户号 2 商户名称  3 子商户号 4 流水号 5 商户订单号 6 交易金额 7清分金额 8 商户手续费 9退款金额 10 结算金额 11交易日期 12清算日期
            unset($resBank[0]); // 注销文件首行
            // 平台对建行单
            $check_time = date("Y-m-d", strtotime($input['vtime'][0]));
            // 银行差异数据
            foreach ($resBank as $val) {
                if(empty($val[11])) continue;
                list($year,$time) = explode(" ", trim($val[11]));
                if($check_time != $year) {
                    $r = [
                        'success' => false,
                        'data' => [],
                        'message' => 'excel数据时间和平台对账时间不符',
                    ];
                    exit(json_encode($r));
                }
            }
        });
        // 获取车场记录
        $p = new PayFee();
        // 获取车场范围
        if (isset($input['park_code']) && !empty($input['park_code'])) {
            $p = $p->whereIn("park_code",$input['park_code']);
        } else {
            $p = $p->whereIn("park_code", $this->park_arr);
        }
        // 支付时间
        if (isset($input['vtime']) && !empty($input['vtime'])) {
            $pay_time = $input['vtime'];
            if (isset($pay_time[0]) && !empty($pay_time[0])) {
                $pay_time[0] = strpos($pay_time[0], '+') ? str_replace('+', '', $pay_time[0]) : $pay_time[0];
                $start_time = strtotime($pay_time[0]);
                $p = $p->where("pay_time", '>=', $start_time);
            }
            if (isset($pay_time[1]) && !empty($pay_time[1])) {
                $pay_time[1] = strpos($pay_time[1], '+') ? str_replace('+', '', $pay_time[1]) : $pay_time[1];
                $start_time = strtotime($pay_time[1]);
                $p = $p->where("pay_time", '<', $start_time);
            }
        }
        $p = $p->whereIn("pay_type", [0,2,3,4]);
        $record = $p->select(['pay_order','pay_amount','pay_type','pay_time','park_code','pay_src_amount',])
            ->orderBy("pay_order", "desc")
            ->get();

        $all_fee = [];
        foreach ($record as &$val) {
            $val->pay_amount = number_format($val->pay_amount / 100, 2, '.', '');
            $val->pay_type_name = isset($all_pay_type[$val->pay_type]) ? $all_pay_type[$val->pay_type] : '';
            $val->pay_time = $val->pay_time ? date("Y-m-d H:i:s", $val->pay_time) : '';
            $park_name = ParkInfo::where("park_code", $val->park_code)->value("parking_name");
            $val->park_name = $park_name ? $park_name : '';
            $val->pay_src_amount = number_format($val->pay_src_amount / 100, 2, '.', '');
            if(substr($val->pay_order,0,2) == '98'){
                $val->pay_type_name = '无感支付';
                $val->pay_order = $val->pay_src_order;
            }
            $all_fee[$val->pay_order] = $val;
        }

        Excel::load($filePath, function ($reader) use ($all_fee,$filePath) {
            $reader = $reader->getSheet(0);
            $resBank = $reader->toArray();
            // 0 序号  1商户号 2 商户名称  3 子商户号 4 流水号 5 商户订单号 6 交易金额 7清分金额 8 商户手续费 9退款金额 10 结算金额 11交易日期 12清算日期
            unset($resBank[0]); // 注销文件首行
            // 平台对建行单
            $jianBank = [];
            $all_bank = [];
            $feild_data = [];
            // 银行差异数据
            foreach ($resBank as $val) {
                if(!array_key_exists(trim($val[5]),$all_fee)){
                    if(empty(trim($val[5]))) continue;
                    $tmp = [];
                    $tmp['pay_order'] = trim($val[5]);
                    $tmp['pay_time'] = $val[11];
                    $tmp['pay_amount'] = $val[6];
                    $tmp['pay_src_amount'] = $val[6];
                    $tmp['pay_type_name'] = '';
                    $tmp['pay_source'] = '银行';
                    $jianBank[] = $tmp;
                }
                $all_bank[trim($val[5])] = $val;
            }
            $platForm = [];
            // 平台差异数据
            foreach ($all_fee as $key=>$val) {
                if(!array_key_exists(trim($key),$all_bank)){
                    if(empty($val->pay_order)) continue;
                    $platForm[] = $val;
                    $tmp = [];
                    $tmp['pay_order'] = $val->pay_order;
                    $tmp['pay_time'] = $val->pay_time;
                    $tmp['pay_amount'] = $val->pay_amount;
                    $tmp['pay_src_amount'] = $val->pay_src_amount;
                    $tmp['pay_type_name'] = $val->pay_type_name;
                    $tmp['pay_source'] = '平台';
                    $jianBank[] = $tmp;
                }
            }
//            if(file_exists($filePath)) unlink($filePath);
            $r = [
                'success'=>true,
                'data'=>$jianBank,
                'message'=>count($jianBank)==0?'对比结束,沒有异常订单':'对比结束',
            ];
            exit(json_encode($r));

        });
        return $this->sendResponse([], '对比失败');
    }

    /**
     * @param Request $request
     * @return mixed
     */
    public function bankFile(Request $request){
        $file = $request->file('file');
        $filename = $request->input('file_name','');
        $filePath = storage_path() . '/app/uploads/'.$filename;
        $this->log($filename);
        if(file_exists($filePath) && $filename) {
            unlink($filePath);
        }
        // 文件是否上传成功
        if ($file->isValid()) {
            // 获取文件相关信息
            $originalName = $file->getClientOriginalName(); // 文件原名
            $ext = $file->getClientOriginalExtension();     // 扩展名
            $realPath = $file->getRealPath();   //临时文件的绝对路径
            $type = $file->getClientMimeType();     // image/jpeg
            $filePath = storage_path().'/app/uploads/fee.'.$ext;
            // 上传文件
            $filename =  time().'.' . $ext;
            if($ext != 'csv'){
                return $this->sendError('必须是csv格式文件');
            }
            // 使用我们新建的uploads本地存储空间（目录）
            //这里的uploads是配置文件的名称
            $bool = Storage::disk('uploads')->put($filename, fopen($request->file('file')->getRealPath(), 'r'));
            if($bool){
                return $this->sendResponse($filename, '上传成功');
            }
            return $this->sendError('上传失败');
        }
        return $this->sendError('无效文件');
    }

    /**
     *
     *  获取银行excel
     */
    public function sendBankFile(Request $request){
        $res = $this->sendBankInfo();
        $arr = explode("&",$res);
        $data = [];
        foreach ($arr as $val) {
            $tmp = explode("=",$val);
            $data[$tmp[0]] = $tmp[1];
        }
        if($data['respCode'] == '00'){

        }
        return $this->sendResponse($data, '发送成功');
    }

    public function getBankInfo(Request $request){
        \Log::info("bank excel success".json_encode($request));
        \Log::info("post data ".json_encode($_POST));
        $this->downBankfile($_POST);
        return $this->sendResponse($request, '成功');
    }

    /**
     * @param Request $request
     * @return mixed
     *
     * 和银行对账
     */
    public function reportBankFee(Request $request){
        set_time_limit(0);
        ini_set('memory_limit', '1024M');
        $input = $request->all();
        $all_pay_type = config('custom.pay_name');
        $bank_time = date("Ymd", strtotime($input['vtime'][1]));
//        $filePath = storage_path()."/app/bank/trans-1000000001-$bank_time.xls";
        $filePath = storage_path()."/app/bank/settle-1000000001-$bank_time-1.xls";
        if(!file_exists($filePath)){
            $this->dateBankInfo($bank_time);
            return $this->sendError('文件不存在,正在抓取，请稍后再试');
        }
        Excel::load($filePath, function ($reader) use ($input,$filePath) {
            $reader = $reader->getSheet(0);
            $resBank = $reader->toArray();
            // 0 序号  1商户号 2 商户名称  3 子商户号 4 流水号 5 商户订单号 6 交易金额 7清分金额 8 商户手续费 9退款金额 10 结算金额 11交易日期 12清算日期
            for($i=0; $i<=10; $i++) {
                unset($resBank[$i]); // 注销文件首行
            }
            // 平台对建行单
            $check_time = date("Y-m-d", strtotime($input['vtime'][0]));
            // 银行差异数据
            foreach ($resBank as $key=>$val) {
                if(empty($val[5])) continue;
                list($year,$time) = explode(" ", trim($val[5]));
                if($check_time != $year) {
                    unset($resBank[$key]);
                }
            }
        });
        // 获取车场记录
        $p = new PayFee();
        // 获取车场范围
        if (isset($input['park_code']) && !empty($input['park_code'])) {
            $p = $p->whereIn("park_code",$input['park_code']);
        } else {
            $p = $p->whereIn("park_code", $this->park_arr);
        }
        // 支付时间
        if (isset($input['vtime']) && !empty($input['vtime'])) {
            $pay_time = $input['vtime'];
            if (isset($pay_time[0]) && !empty($pay_time[0])) {
                $pay_time[0] = strpos($pay_time[0], '+') ? str_replace('+', '', $pay_time[0]) : $pay_time[0];
                $start_time = strtotime($pay_time[0]);
                $p = $p->where("pay_time", '>=', $start_time);
            }
            if (isset($pay_time[1]) && !empty($pay_time[1])) {
                $pay_time[1] = strpos($pay_time[1], '+') ? str_replace('+', '', $pay_time[1]) : $pay_time[1];
                $start_time = strtotime($pay_time[1]);
                $p = $p->where("pay_time", '<', $start_time);
            }
        }
        $p = $p->whereIn("pay_type", [0,2,3,4]);
        $record = $p->select(['pay_order','pay_src_order','pay_amount','pay_type','pay_time','park_code','pay_src_amount',])
            ->orderBy("pay_order", "desc")
            ->get();

        $all_fee = [];
        foreach ($record as &$val) {
            if(empty($val->pay_order)) continue;
            $val->pay_amount = number_format($val->pay_amount / 100, 2, '.', '');
            $val->pay_type_name = isset($all_pay_type[$val->pay_type]) ? $all_pay_type[$val->pay_type] : '';
            $val->pay_time = $val->pay_time ? date("Y-m-d H:i:s", $val->pay_time) : '';
            $park_name = ParkInfo::where("park_code", $val->park_code)->value("parking_name");
            $val->park_name = $park_name ? $park_name : '';
            $val->pay_src_amount = number_format($val->pay_src_amount / 100, 2, '.', '');
            if(substr(trim($val->pay_order),0,2) == '98'){
                $val->pay_type_name = '无感支付';
                $val->pay_order = $val->pay_src_order;
                $all_fee[$val->pay_src_order] = $val;
            } else {
                $all_fee[$val->pay_order] = $val;
            }
        }

        Excel::load($filePath, function ($reader) use ($input,$all_fee,$filePath) {
            $reader = $reader->getSheet(0);
            $resBank = $reader->toArray();
            // 0 序号  1商户号 2 商户名称  3 子商户号 4 流水号 5 商户订单号 6 交易金额 7清分金额 8 商户手续费 9退款金额 10 结算金额 11交易日期 12清算日期
            for($i=0; $i<=10; $i++) {
                unset($resBank[$i]); // 注销文件首行
            }
            // 平台对建行单
            $jianBank = [];
            $all_bank = [];
            $feild_data = [];

            // 平台对建行单
            $check_time = date("Y-m-d", strtotime($input['vtime'][0]));
            // 银行差异数据
            foreach ($resBank as $key=>$val) {
                if(substr(trim($val[3]),0,2) == 20){
                    $val[4] = trim($val[3]);
                }
                if(!array_key_exists(trim($val[4]),$all_fee)){
                    if(empty(trim($val[4]))) continue;
                    list($year,$time) = explode(" ", trim($val[5]));
                    if($check_time != $year) {
                        unset($resBank[$key]);
                        continue;
                    }
                    $tmp = [];
                    $tmp['pay_order'] = trim($val[4]);
                    $tmp['pay_time'] = $val[5];
                    $tmp['pay_amount'] = $val[6];
                    $tmp['pay_src_amount'] = $val[6];
                    $tmp['pay_type_name'] = '';
                    $tmp['pay_source'] = '银行';
                    $jianBank[] = $tmp;
                }
                $all_bank[trim($val[4])] = $val;
            }
            $platForm = [];
            // 平台差异数据
            foreach ($all_fee as $key=>$val) {
                if(!array_key_exists(trim($key),$all_bank)){
                    if(empty($val->pay_order)) continue;
                    $platForm[] = $val;
                    $tmp = [];
                    $tmp['pay_order'] = $val->pay_order;
                    $tmp['pay_time'] = $val->pay_time;
                    $tmp['pay_amount'] = $val->pay_amount;
                    $tmp['pay_src_amount'] = $val->pay_src_amount;
                    $tmp['pay_type_name'] = $val->pay_type_name;
                    $tmp['pay_source'] = '平台';
                    $jianBank[] = $tmp;
                }
            }
//            if(file_exists($filePath)) unlink($filePath);
            $r = [
                'success'=>true,
                'data'=>$jianBank,
                'message'=>count($jianBank)==0?'对比结束,沒有异常订单':'对比结束',
            ];
            exit(json_encode($r));

        });
        return $this->sendResponse([], '对比失败');
    }

    /**
     * @param Request $request
     * @return mixed
     *
     * 和银行对账
     */
    public function reportEtcFee(Request $request){
        set_time_limit(0);
        $input = $request->all();
        $all_pay_type = config('custom.pay_name');
        $etc_api = config('custom.etc_api_url');
        $etc_park = config('custom.etc_park');
        $bank_time = date("Y-m-d", strtotime($input['vtime'][0]));
        $all_fee = [];
        $resEtc = [];
        foreach ($etc_park as $p) {
            $data = curl_post($etc_api, ["park"=>$p,"date"=>$bank_time]);
            $data = json_decode($data, true);
            \Log::error("etc-payfee:----".$p.'----------'.$data['error']);
            if($data['error'] != "ok") continue;
            foreach ($data['data']['list'] as $val) {
                if($val['fee']==0) continue;
                $key = md5($val['vpln'].$val['fee'].$val['date']);
                $resEtc[$key] = $val;
            }
        }
        // 获取车场记录
        $p = new PayFee();
        // 获取车场范围
        if (isset($input['park_code']) && !empty($input['park_code'])) {
            $p = $p->whereIn("park_code",$input['park_code']);
        } else {
            $p = $p->whereIn("park_code", $this->park_arr);
        }
        // 支付时间
        if (isset($input['vtime']) && !empty($input['vtime'])) {
            $pay_time = $input['vtime'];
            if (isset($pay_time[0]) && !empty($pay_time[0])) {
                $pay_time[0] = strpos($pay_time[0], '+') ? str_replace('+', '', $pay_time[0]) : $pay_time[0];
                $start_time = strtotime($pay_time[0]);
                $p = $p->where("pay_time", '>=', $start_time);
            }
            if (isset($pay_time[1]) && !empty($pay_time[1])) {
                $pay_time[1] = strpos($pay_time[1], '+') ? str_replace('+', '', $pay_time[1]) : $pay_time[1];
                $start_time = strtotime($pay_time[1]);
                $p = $p->where("pay_time", '<', $start_time);
            }
        }
        $p = $p->whereIn("pay_type", [5])->where("pay_amount",">",0);
        $record = $p->select(['pay_order','pay_src_order','vpl_number','pay_amount','pay_type','pay_time','park_code','pay_src_amount','out_time'])
            ->orderBy("pay_order", "desc")
            ->get();

        $all_fee = [];
        foreach ($record as &$val) {
            if(empty($val->pay_order)) continue;
            $val->pay_amount = number_format($val->pay_amount / 100, 2, '.', '');
            $val->pay_type_name = isset($all_pay_type[$val->pay_type]) ? $all_pay_type[$val->pay_type] : '';
            $val->pay_time = $val->pay_time ? date("Y-m-d H:i:s", $val->pay_time) : '';
            $park_name = ParkInfo::where("park_code", $val->park_code)->value("parking_name");
            $val->park_name = $park_name ? $park_name : '';
            $val->pay_src_amount = number_format($val->pay_src_amount / 100, 2, '.', '');
            $key = md5($val->vpl_number.($val->pay_amount*100).date("Y-m-d H:i:s",$val->out_time));
            $all_fee[$key] = $val;

        }
        $jianBank = [];
        // ETC差异数据
        foreach ($resEtc as $key=>$val) {
            if(!array_key_exists(trim($key),$all_fee)){
                $tmp = [];
                $tmp['vpl_number'] = trim($val['vpln']);
                $tmp['pay_amount'] = $val['fee']/100;
                $tmp['out_time'] = $val['date'];
                $tmp['pay_source'] = 'ETC';
                $jianBank[] = $tmp;
            }
            $all_bank[$key] = $val;
        }

        // 平台差异数据
        foreach ($all_fee as $key=>$val) {
            if(!isset($all_bank)) continue;
            if(!array_key_exists(trim($key),$all_bank)){
                if(empty($val->pay_order)) continue;
                $tmp = [];
                $tmp['pay_order'] = $val->pay_order;
                $tmp['pay_time'] = $val->pay_time;
                $tmp['pay_src_amount'] = $val->pay_src_amount;
                $tmp['pay_type_name'] = $val->pay_type_name;
                $tmp['vpl_number'] = $val->vpl_number;
                $tmp['pay_amount'] = $val->pay_amount;
                $tmp['out_time'] = date("Y-m-d H:i:s",$val->out_time);
                $tmp['pay_source'] = '平台';
                $jianBank[] = $tmp;
            }
        }
        return $this->sendResponse($jianBank, '对比结束');
    }

    public function reportBankFeeBak(Request $request){
        set_time_limit(0);
        $input = $request->all();
        $all_pay_type = config('custom.pay_name');
        $bank_time = date("Ymd", strtotime($input['vtime'][0]));
        $filePath = storage_path()."/app/bank/trans-1000000001-$bank_time.xls";
        $filePath = storage_path()."/app/bank/settle-1000000001-20180601-1.xls";
        if(!file_exists($filePath)){
            $this->dateBankInfo($bank_time);
            return $this->sendError('文件不存在,正在抓取，请稍后再试');
        }
        Excel::load($filePath, function ($reader) use ($input,$filePath) {
            $reader = $reader->getSheet(0);
            $resBank = $reader->toArray();
            // 0 序号  1商户号 2 商户名称  3 子商户号 4 流水号 5 商户订单号 6 交易金额 7清分金额 8 商户手续费 9退款金额 10 结算金额 11交易日期 12清算日期
            unset($resBank[0]); // 注销文件首行
            // 平台对建行单
            $check_time = date("Y-m-d", strtotime($input['vtime'][0]));
            // 银行差异数据
            foreach ($resBank as $val) {
                if(empty($val[9])) continue;
                list($year,$time) = explode(" ", trim($val[9]));
                if($check_time != $year) {
                    $r = [
                        'success' => false,
                        'data' => [],
                        'message' => 'excel数据时间和平台对账时间不符',
                    ];
                    exit(json_encode($r));
                }
            }
        });
        // 获取车场记录
        $p = new PayFee();
        // 获取车场范围
        if (isset($input['park_code']) && !empty($input['park_code'])) {
            $p = $p->whereIn("park_code",$input['park_code']);
        } else {
            $p = $p->whereIn("park_code", $this->park_arr);
        }
        // 支付时间
        if (isset($input['vtime']) && !empty($input['vtime'])) {
            $pay_time = $input['vtime'];
            if (isset($pay_time[0]) && !empty($pay_time[0])) {
                $pay_time[0] = strpos($pay_time[0], '+') ? str_replace('+', '', $pay_time[0]) : $pay_time[0];
                $start_time = strtotime($pay_time[0]);
                $p = $p->where("pay_time", '>=', $start_time);
            }
            if (isset($pay_time[1]) && !empty($pay_time[1])) {
                $pay_time[1] = strpos($pay_time[1], '+') ? str_replace('+', '', $pay_time[1]) : $pay_time[1];
                $start_time = strtotime($pay_time[1]);
                $p = $p->where("pay_time", '<', $start_time);
            }
        }
        $p = $p->whereIn("pay_type", [0,2,3,4]);
        $record = $p->select(['pay_order','pay_amount','pay_type','pay_time','park_code','pay_src_amount',])
            ->orderBy("pay_order", "desc")
            ->get();

        $all_fee = [];
        foreach ($record as &$val) {
            $val->pay_amount = number_format($val->pay_amount / 100, 2, '.', '');
            $val->pay_type_name = isset($all_pay_type[$val->pay_type]) ? $all_pay_type[$val->pay_type] : '';
            $val->pay_time = $val->pay_time ? date("Y-m-d H:i:s", $val->pay_time) : '';
            $park_name = ParkInfo::where("park_code", $val->park_code)->value("parking_name");
            $val->park_name = $park_name ? $park_name : '';
            $val->pay_src_amount = number_format($val->pay_src_amount / 100, 2, '.', '');
            if(substr($val->pay_order,0,2) == '98'){
                $val->pay_type_name = '无感支付';
                $val->pay_order = $val->pay_src_order;
            }
            $all_fee[$val->pay_order] = $val;
        }

        Excel::load($filePath, function ($reader) use ($all_fee,$filePath) {
            $reader = $reader->getSheet(0);
            $resBank = $reader->toArray();
            // 0 序号  1商户号 2 商户名称  3 子商户号 4 流水号 5 商户订单号 6 交易金额 7清分金额 8 商户手续费 9退款金额 10 结算金额 11交易日期 12清算日期
            unset($resBank[0]); // 注销文件首行
            // 平台对建行单
            $jianBank = [];
            $all_bank = [];
            $feild_data = [];
            // 银行差异数据
            foreach ($resBank as $val) {
                if(!array_key_exists(trim($val[3]),$all_fee)){
                    if(empty(trim($val[3]))) continue;
                    $tmp = [];
                    $tmp['pay_order'] = trim($val[3]);
                    $tmp['pay_time'] = $val[9];
                    $tmp['pay_amount'] = $val[7];
                    $tmp['pay_src_amount'] = $val[7];
                    $tmp['pay_type_name'] = '';
                    $tmp['pay_source'] = '银行';
                    $jianBank[] = $tmp;
                }
                $all_bank[trim($val[3])] = $val;
            }
            $platForm = [];
            // 平台差异数据
            foreach ($all_fee as $key=>$val) {
                if(!array_key_exists(trim($key),$all_bank)){
                    if(empty($val->pay_order)) continue;
                    $platForm[] = $val;
                    $tmp = [];
                    $tmp['pay_order'] = $val->pay_order;
                    $tmp['pay_time'] = $val->pay_time;
                    $tmp['pay_amount'] = $val->pay_amount;
                    $tmp['pay_src_amount'] = $val->pay_src_amount;
                    $tmp['pay_type_name'] = $val->pay_type_name;
                    $tmp['pay_source'] = '平台';
                    $jianBank[] = $tmp;
                }
            }
//            if(file_exists($filePath)) unlink($filePath);
            $r = [
                'success'=>true,
                'data'=>$jianBank,
                'message'=>count($jianBank)==0?'对比结束,沒有异常订单':'对比结束',
            ];
            exit(json_encode($r));

        });
        return $this->sendResponse([], '对比失败');
    }

    function getEtcData(Request $request){
        $data  = $request->post("data");
        $park_code  = $request->post("park_code");
        $date  = $request->post("date");
        if(empty($data)) return \Response::json(['message' => '无数据']);
        if(empty($park_code)) return \Response::json(['message' => '无指定车场']);
        if(empty($date)) return \Response::json(['message' => '无指定日期']);
        \Log::error("etc_data: park_code:".$park_code."; data:". $data);
        $time = date("Y-m-d 00:00:00", strtotime($date));
        \DB::table("etc_data")->where("time", $time)->where("park_code", $park_code)->delete();
        \DB::table("etc_data")->insert(["data"=>$data, "park_code"=>$park_code,"time"=>$time]);
        return \Response::json(['message' => 'ok']);
    }


}
